/*
 * @(#)GenerateDAO.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Types;
import java.util.Calendar;
import java.util.Hashtable;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
  GenerateDAO.java

  Code generator for generating DAObject java files.

    @author           Rollo Chan
    @company          DCIVision Limited
    @creation date    05/07/2003
    @version          $Revision: 1.37.32.1 $
*/

public class GenerateDAO {

  public static final String REVISION = "$Revision: 1.37.32.1 $";

  static String author = null;
  static String srcPath = null;
  static String driverStr = null;
  static String dbConnStr = null;
  static String dbUsername = null;
  static String dbPassword = null;

  static String packageName = "com.dcivision.";

  static final String JAVA_INTEGER = "Integer";
  static final String JAVA_DECIMAL = "Float";
  static final String JAVA_STRING = "String";
  static final String JAVA_DATE = "Timestamp";

  static Connection conn = null;
  static String sqlStat = null;
  static Statement stat = null;
  static ResultSet rs = null;
  static ResultSetMetaData rsmd = null;

  static PrintStream out = null;
  static String srcFileName = null;
  static String tableName = null;
  static String className = "";

  static Hashtable exceptionFields = new Hashtable();
  static boolean hasRecordStatus = false;
  private static final Log log = LogFactory.getLog(GenerateDAO.class);
  public static void main(String[] argv) throws Exception {
    // Load properties in the "src" directory.
    java.util.Properties props = new java.util.Properties();
    String path = new GenerateDAO().getClass().getProtectionDomain().getCodeSource().getLocation().toString().substring(6);
    if (!path.endsWith("/")) {
      path += "/";
    }
    path += "generate.properties";
    java.io.FileInputStream fis = new java.io.FileInputStream(new java.io.File(path));
    props.load(fis);

    author = props.getProperty("author");
    srcPath = props.getProperty("srcPath");
    driverStr = props.getProperty("driverStr");
    dbConnStr = props.getProperty("dbConnStr");
    dbUsername = props.getProperty("dbUsername");
    dbPassword = props.getProperty("dbPassword");

    // Initialize those common fields which NOT need to generate.
    exceptionFields.put("ID", "Y");
    exceptionFields.put("CREATOR_ID", "Y");
    exceptionFields.put("CREATE_DATE", "Y");
    exceptionFields.put("UPDATER_ID", "Y");
    exceptionFields.put("UPDATE_DATE", "Y");
    exceptionFields.put("UPDATE_COUNT", "Y");
    exceptionFields.put("RECORD_STATUS", "Y");
    hasRecordStatus = true;

    // Check the usage.
    if (argv.length != 2 && argv.length != 3) {
      System.out.println("Usage: java GenerateDAO <TABLE_NAME> <PACKAGE_NAME>");
      return;
    }

    // Initialize file and class settings.
    argv[0] = argv[0].toLowerCase();
    String[] tmpAry = splitString(argv[0], "_");
    for (int i = 0; i < tmpAry.length; i++) {
      className += Character.toUpperCase(tmpAry[i].charAt(0)) + tmpAry[i].substring(1);
    }

    tableName = argv[0];
    packageName += argv[1];
    srcFileName = srcPath + "/src/" + replaceString(packageName, ".", "/") + "/dao/" + className + "DAObject.java";

    // Open source file.
    openSrcFile();

    // Initialize database connection.
    openDBConn();

    // Print the program header.
    printHeader();

    // Print Select function.
    printGetObjectByID();

    // Print Get List function.
    printGetList();

    // Print Get List With No Argument function.
    printGetList1();

    // Print Validation functions.
    printValidation();

    // Print Insert function.
    printCreateObject();

    // Print Update function.
    printUpdateObject();

    // Print Update function.
    printDeleteObject();

    // Print Prepare Audit Trail function
    printAuditTrail();

    // Print Warning function.
    printWarning();

    // Initialize database connection.
    closeDBConn();

    // Print the program footer.
    printFooter();

    // Close source file.
    closeSrcFile();
  }

  public static void printValidation() throws Exception {
    out.println("  protected void validateInsert(AbstractBaseObject obj) throws ApplicationException {");
    out.println("  }");
    out.println();
    out.println("  protected void validateUpdate(AbstractBaseObject obj) throws ApplicationException {");
    out.println("  }");
    out.println();
    out.println("  protected void validateDelete(AbstractBaseObject obj) throws ApplicationException {");
    out.println("  }");
    out.println();
  }

  public static void printAuditTrail() throws Exception {
    out.println("  protected synchronized void auditTrail(String opMode, AbstractBaseObject obj) throws ApplicationException {");
    out.println("    Vector oldValues = new Vector();");
    out.println("    Vector newValues = new Vector();");
    out.println("    " + className + " tmp" + className + " = (" + className + ")this.oldValue;");
    out.println();
    out.println("    if (tmp" + className + " != null) {");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      out.println("      oldValues.add(toAuditTrailValue(tmp" + className + ".get" + getVariableName(rsmd.getColumnName(i), true) + "()));");
    }
    out.println("    }");
    out.println();

    out.println("    tmp" + className + " = (" + className + ")obj;");
    out.println("    if (tmp" + className + " != null) {");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      out.println("      newValues.add(toAuditTrailValue(tmp" + className + ".get" + getVariableName(rsmd.getColumnName(i), true) + "()));");
    }
    out.println("    }");
    out.println();
    out.println("    auditTrailBase(opMode, oldValues, newValues);");
    out.println("  }");
    out.println();
  }

  public static void printWarning() throws Exception {
    out.println("  /***********************************************************************");
    out.println("   * DON'T Modify the codes above unless you know what you are doing!!!  *");
    out.println("   * Put your own functions beblow.                                      *");
    out.println("   * For FINDER methods, the function name should be in the notation:    *");
    out.println("   *   public Object getObjectBy<Criteria>()                             *");
    out.println("   *   - e.g. public Object getObjectByCode()                            *");
    out.println("   *   public List getListBy<Criteria>()                                 *");
    out.println("   *   - e.g. public List getListByUserID()                              *");
    out.println("   * For OPERATION methods, the function name should be in the notation: *");
    out.println("   *   public void <Operation>ObjectBy<Criteria>()                       *");
    out.println("   *   - e.g. public void deleteObjectByCode()                           *");
    out.println("   *   public void <Operation>ListBy<Criteria>()                         *");
    out.println("   *   - e.g. public void deleteListByUserID()                           *");
    out.println("   ***********************************************************************/");
    out.println();
  }

  public static void printDeleteObject() throws Exception {
    out.println("  protected synchronized AbstractBaseObject delete(AbstractBaseObject obj) throws ApplicationException {");
    out.println("    PreparedStatement preStat = null;");
    out.println("    StringBuffer sqlStat = new StringBuffer();");
    out.println("    " + className + " tmp" + className + " = (" + className + ")((" + className + ")obj).clone();");
    out.println();
    out.println("    synchronized(dbConn) {");
    out.println("      try {");
    out.println("        int updateCnt = 0;");
    out.println("        sqlStat.append(\"DELETE \");");
    out.println("        sqlStat.append(\"FROM   " + tableName.toUpperCase() + " \");");
    out.println("        sqlStat.append(\"WHERE  ID=? AND UPDATE_COUNT=? \");");
    out.println("        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);");
    out.println("        setPrepareStatement(preStat, 1, tmp" + className + ".getID());");
    out.println("        setPrepareStatement(preStat, 2, tmp" + className + ".getUpdateCount());");
    out.println("        updateCnt = preStat.executeUpdate();");
    out.println("        if (updateCnt == 0) {");
    out.println("          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);");
    out.println("        } else {");
    out.println("          return(tmp" + className + ");");
    out.println("        }");
    out.println("      } catch (ApplicationException appEx) {");
    out.println("        throw appEx;");
    out.println("      } catch (SQLException sqle) {");
    out.println("        log.error(sqle, sqle);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());");
    out.println("      } catch (Exception e) {");
    out.println("        log.error(e, e);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);");
    out.println("      } finally {");
    out.println("        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }");
    out.println("      }");
    out.println("    }");
    out.println("  }");
    out.println();
  }

  public static void printUpdateObject() throws Exception {
    out.println("  protected synchronized AbstractBaseObject update(AbstractBaseObject obj) throws ApplicationException {");
    out.println("    PreparedStatement preStat = null;");
    out.println("    StringBuffer sqlStat = new StringBuffer();");
    out.println("    " + className + " tmp" + className + " = (" + className + ")((" + className + ")obj).clone();");
    out.println();
    out.println("    synchronized(dbConn) {");
    out.println("      try {");
    out.println("        int updateCnt = 0;");
    out.println("        Timestamp currTime = Utility.getCurrentTimestamp();");
    out.println("        sqlStat.append(\"UPDATE " + tableName.toUpperCase() + " \");");
    out.print("        sqlStat.append(\"SET  ");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      if (!rsmd.getColumnName(i).toUpperCase().equals("CREATE_DATE") && !rsmd.getColumnName(i).toUpperCase().equals("CREATOR_ID") && !rsmd.getColumnName(i).toUpperCase().equals("ID") && !rsmd.getColumnName(i).toUpperCase().equals("RECORD_STATUS")) {
        out.print(rsmd.getColumnName(i) + "=?");
        if (i != rsmd.getColumnCount()) {
          out.print(", ");
        }
      }
    }
    out.println(" \");");
    out.println("        sqlStat.append(\"WHERE  ID=? AND UPDATE_COUNT=? \");");
    out.println("        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);");
    int idx = 1;
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      if (exceptionFields.get(rsmd.getColumnName(i).toUpperCase()) == null) {
        out.println("        setPrepareStatement(preStat, " + idx + ", tmp" + className + ".get" + getVariableName(rsmd.getColumnName(i), true) + "());");
        idx++;
      }
    }
    out.println("        setPrepareStatement(preStat, " + (idx++) + ", new Integer(tmp" + className + ".getUpdateCount().intValue() + 1));");
    out.println("        setPrepareStatement(preStat, " + (idx++) + ", sessionContainer.getUserRecordID());");
    out.println("        setPrepareStatement(preStat, " + (idx++) + ", currTime);");
    out.println("        setPrepareStatement(preStat, " + (idx++) + ", tmp" + className + ".getID());");
    out.println("        setPrepareStatement(preStat, " + (idx++) + ", tmp" + className + ".getUpdateCount());");
    out.println("        updateCnt = preStat.executeUpdate();");
    out.println("        if (updateCnt == 0) {");
    out.println("          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);");
    out.println("        } else {");
    out.println("          tmp" + className + ".setUpdaterID(sessionContainer.getUserRecordID());");
    out.println("          tmp" + className + ".setUpdateDate(currTime);");
    out.println("          tmp" + className + ".setUpdateCount(new Integer(tmp" + className + ".getUpdateCount().intValue() + 1));");
    out.println("          tmp" + className + ".setCreatorName(UserInfoFactory.getUserFullName(tmp" + className + ".getCreatorID()));");
    out.println("          tmp" + className + ".setUpdaterName(UserInfoFactory.getUserFullName(tmp" + className + ".getUpdaterID()));");
    out.println("          return(tmp" + className + ");");
    out.println("        }");
    out.println("      } catch (ApplicationException appEx) {");
    out.println("        throw appEx;");
    out.println("      } catch (SQLException sqle) {");
    out.println("        log.error(sqle, sqle);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());");
    out.println("      } catch (Exception e) {");
    out.println("        log.error(e, e);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);");
    out.println("      } finally {");
    out.println("        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }");
    out.println("      }");
    out.println("    }");
    out.println("  }");
    out.println();
  }

  public static void printCreateObject() throws Exception {
    out.println("  protected synchronized AbstractBaseObject insert(AbstractBaseObject obj) throws ApplicationException {");
    out.println("    PreparedStatement preStat = null;");
    out.println("    StringBuffer sqlStat = new StringBuffer();");
    out.println("    " + className + " tmp" + className + " = (" + className + ")((" + className + ")obj).clone();");
    out.println();
    out.println("    synchronized(dbConn) {");
    out.println("      try {");
    out.println("        " + JAVA_INTEGER + " nextID = getNextPrimaryID();");
    out.println("        Timestamp currTime = Utility.getCurrentTimestamp();");
    out.println("        sqlStat.append(\"INSERT \");");
    out.print("        sqlStat.append(\"INTO   " + tableName.toUpperCase() + "(");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      out.print(rsmd.getColumnName(i));
      if (i != rsmd.getColumnCount()) {
        out.print(", ");
      }
    }
    out.println(") \");");
    out.print("        sqlStat.append(\"VALUES (");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      out.print("?");
      if (i != rsmd.getColumnCount()) {
        out.print(", ");
      }
    }
    out.println(") \");");
    out.println("        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      if (i == 1) {
        out.println("        setPrepareStatement(preStat, 1, nextID);");
      } else if (exceptionFields.get(rsmd.getColumnName(i).toUpperCase()) == null) {
        out.println("        setPrepareStatement(preStat, " + i + ", tmp" + className + ".get" + getVariableName(rsmd.getColumnName(i), true) + "());");
      }
    }

    int endIdx = rsmd.getColumnCount() - 4;
    if (hasRecordStatus) {
      endIdx--;
      out.println("        setPrepareStatement(preStat, " + (endIdx++) + ", GlobalConstant.RECORD_STATUS_ACTIVE);");
    }
    out.println("        setPrepareStatement(preStat, " + (endIdx++) + ", new Integer(0));");
    out.println("        setPrepareStatement(preStat, " + (endIdx++) + ", sessionContainer.getUserRecordID());");
    out.println("        setPrepareStatement(preStat, " + (endIdx++) + ", currTime);");
    out.println("        setPrepareStatement(preStat, " + (endIdx++) + ", sessionContainer.getUserRecordID());");
    out.println("        setPrepareStatement(preStat, " + (endIdx++) + ", currTime);");
    out.println("        preStat.executeUpdate();");
    out.println("        tmp" + className + ".set" + getIDFieldName() + "(nextID);");
    out.println("        tmp" + className + ".setCreatorID(sessionContainer.getUserRecordID());");
    out.println("        tmp" + className + ".setCreateDate(currTime);");
    out.println("        tmp" + className + ".setUpdaterID(sessionContainer.getUserRecordID());");
    out.println("        tmp" + className + ".setUpdateDate(currTime);");
    out.println("        tmp" + className + ".setUpdateCount(new Integer(0));");
    out.println("        tmp" + className + ".setCreatorName(UserInfoFactory.getUserFullName(tmp" + className + ".getCreatorID()));");
    out.println("        tmp" + className + ".setUpdaterName(UserInfoFactory.getUserFullName(tmp" + className + ".getUpdaterID()));");
    out.println("        return(tmp" + className + ");");
    out.println("      } catch (ApplicationException appEx) {");
    out.println("        throw appEx;");
    out.println("      } catch (SQLException sqle) {");
    out.println("        log.error(sqle, sqle);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());");
    out.println("      } catch (Exception e) {");
    out.println("        log.error(e, e);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);");
    out.println("      } finally {");
    out.println("        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }");
    out.println("      }");
    out.println("    }");
    out.println("  }");
    out.println();
  }

  public static void printGetObjectByID() throws Exception {
    out.println("  protected synchronized AbstractBaseObject getByID(" + JAVA_INTEGER + " id) throws ApplicationException {");
    out.println("    PreparedStatement preStat = null;");
    out.println("    ResultSet rs = null;");
    out.println("    StringBuffer sqlStat = new StringBuffer();");
    out.println();
    out.println("    synchronized(dbConn) {");
    out.println("      try {");
    out.print("        sqlStat.append(\"SELECT ");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      out.print("A." + rsmd.getColumnName(i));
      if (i != rsmd.getColumnCount()) {
        out.print(", ");
      }
    }
    out.println(" \");");
    out.println("        sqlStat.append(\"FROM   " + tableName.toUpperCase() + " A \");");
    out.println("        sqlStat.append(\"WHERE  A." + getIDFieldName() + " = ? AND A.RECORD_STATUS = ? \");");
    out.println("        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);");
    out.println("        this.setPrepareStatement(preStat, 1, id);");
    out.println("        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);");
    out.println("        rs = preStat.executeQuery();");
    out.println("        if (rs.next()) {");
    out.println("          " + className + " tmp" + className + " = new " + className + "();");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      out.println("          tmp" + className + ".set" + getVariableName(rsmd.getColumnName(i), true) + "(getResultSet" + getJavaType(rsmd, i, true) + "(rs, \"" + rsmd.getColumnName(i) + "\"));");
    }
    out.println("          tmp" + className + ".setCreatorName(UserInfoFactory.getUserFullName(tmp" + className + ".getCreatorID()));");
    out.println("          tmp" + className + ".setUpdaterName(UserInfoFactory.getUserFullName(tmp" + className + ".getUpdaterID()));");
    out.println("          return(tmp" + className + ");");
    out.println("        } else {");
    out.println("          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);");
    out.println("        }");
    out.println("      } catch (ApplicationException appEx) {");
    out.println("        throw appEx;");
    out.println("      } catch (SQLException sqle) {");
    out.println("        log.error(sqle, sqle);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());");
    out.println("      } catch (Exception e) {");
    out.println("        log.error(e, e);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);");
    out.println("      } finally {");
    out.println("        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }");
    out.println("        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }");
    out.println("      }");
    out.println("    }");
    out.println("  }");
    out.println();
  }

  public static void printGetList() throws Exception {
    out.println("  protected synchronized List getList(AbstractSearchForm searchForm) throws ApplicationException {");
    out.println("    PreparedStatement preStat = null;");
    out.println("    PreparedStatement preStatCnt = null;");
    out.println("    ResultSet rs = null;");
    out.println("    ResultSet rsCnt = null;");
    out.println("    StringBuffer sqlStat = new StringBuffer();");
    out.println("    StringBuffer sqlStatCnt = new StringBuffer();");
    out.println("    List result = new ArrayList();");
    out.println("    int totalNumOfRecord = 0;");
    out.println("    int rowLoopCnt = 0;");
    out.println("    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());");
    out.println("    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());");
    out.println();
    out.println("    synchronized(dbConn) {");
    out.println("      try {");
    out.print("        sqlStat.append(\"SELECT ");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      out.print("A." + rsmd.getColumnName(i));
      if (i != rsmd.getColumnCount()) {
        out.print(", ");
      }
    }
    out.println(" \");");
    out.println("        sqlStat.append(\"FROM   " + tableName.toUpperCase() + " A \");");
    out.println("        sqlStat.append(\"WHERE  A.RECORD_STATUS = ? \");");
    out.println();
    out.println("        // Form the WHERE clause for filtering.");
    out.println("        if (searchForm.isSearchable()) {");
    out.println("          String searchField = getSearchColumn(searchForm.getBasicSearchField());");
    out.println("          sqlStat.append(\"AND  \" + searchField + \" \" + searchForm.getBasicSearchType() + \" ? \");");
    out.println("        }");
    out.println();
    out.println("        //format the sql for any 'LIKE' statement contained");
    out.println("        sqlStat = this.getFormattedSQL(sqlStat.toString());");
    out.println();
    out.println("        // Form the ORDER clause for sorting.");
    out.println("        if (searchForm.isSortable()) {");
    out.println("          String sortAttribute = searchForm.getSortAttribute();");
    out.println("          if (sortAttribute.indexOf(\".\") < 0) {");
    out.println("            sortAttribute = \"A.\" + sortAttribute;");
    out.println("          }");
    out.println("          sqlStat.append(\"ORDER BY \" + sortAttribute + \" \" + searchForm.getSortOrder());");
    out.println("        }");
    out.println();
    out.println("        // Get total number of record return.");
    out.println("        sqlStatCnt = this.getSelectCountSQL(sqlStat);");
    out.println("        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);");
    out.println("        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);");
    out.println("        if (searchForm.isSearchable()) {");
    out.println("          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());");
    out.println("          this.setPrepareStatement(preStatCnt, 2, searchKeyword);");
    out.println("        }");
    out.println("        rsCnt = preStatCnt.executeQuery();");
    out.println("        if (rsCnt.next()) {");
    out.println("          totalNumOfRecord = rsCnt.getInt(1);");
    out.println("        }");
    out.println("        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }");
    out.println("        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }");
    out.println();
    out.println("        // Retrieve the result in row basis.");
    out.println("        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);");
    out.println("        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);");
    out.println("        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);");
    out.println("        if (searchForm.isSearchable()) {");
    out.println("          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());");
    out.println("          this.setPrepareStatement(preStat, 2, searchKeyword);");
    out.println("        }");
    out.println("        rs = preStat.executeQuery();");
    out.println();
    out.println("        this.positionCursor(rs, startOffset, pageSize);");
    out.println();
    out.println("        while (rs.next() && rowLoopCnt<pageSize) {");
    out.println("          " + className + " tmp" + className + " = new " + className + "();");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      out.println("          tmp" + className + ".set" + getVariableName(rsmd.getColumnName(i), true) + "(getResultSet" + getJavaType(rsmd, i, true) + "(rs, \"" + rsmd.getColumnName(i) + "\"));");
    }
    out.println("          tmp" + className + ".setCreatorName(UserInfoFactory.getUserFullName(tmp" + className + ".getCreatorID()));");
    out.println("          tmp" + className + ".setUpdaterName(UserInfoFactory.getUserFullName(tmp" + className + ".getUpdaterID()));");
    out.println("          tmp" + className + ".setRecordCount(totalNumOfRecord);");
    out.println("          tmp" + className + ".setRowNum(startOffset++);");
    out.println("          ++rowLoopCnt;");
    out.println("          result.add(tmp" + className + ");");
    out.println("        }");
    out.println("        return(result);");
    out.println("      } catch (ApplicationException appEx) {");
    out.println("        throw appEx;");
    out.println("      } catch (SQLException sqle) {");
    out.println("        log.error(sqle, sqle);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());");
    out.println("      } catch (Exception e) {");
    out.println("        log.error(e, e);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);");
    out.println("      } finally {");
    out.println("        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }");
    out.println("        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }");
    out.println("        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }");
    out.println("        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }");
    out.println("      }");
    out.println("    }");
    out.println("  }");
    out.println();
  }

  public static void printGetList1() throws Exception {
    out.println("  protected synchronized List getList() throws ApplicationException {");
    out.println("    PreparedStatement preStat = null;");
    out.println("    ResultSet rs = null;");
    out.println("    StringBuffer sqlStat = new StringBuffer();");
    out.println("    List result = new ArrayList();");
    out.println();
    out.println("    synchronized(dbConn) {");
    out.println("      try {");
    out.print("        sqlStat.append(\"SELECT ");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      out.print("A." + rsmd.getColumnName(i));
      if (i != rsmd.getColumnCount()) {
        out.print(", ");
      }
    }
    out.println(" \");");
    out.println("        sqlStat.append(\"FROM   " + tableName.toUpperCase() + " A \");");
    out.println("        sqlStat.append(\"WHERE  A.RECORD_STATUS = ? \");");
    out.println("        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);");
    out.println("        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);");
    out.println("        rs = preStat.executeQuery();");
    out.println("        while (rs.next()) {");
    out.println("          " + className + " tmp" + className + " = new " + className + "();");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      out.println("          tmp" + className + ".set" + getVariableName(rsmd.getColumnName(i), true) + "(getResultSet" + getJavaType(rsmd, i, true) + "(rs, \"" + rsmd.getColumnName(i) + "\"));");
    }
    out.println("          tmp" + className + ".setCreatorName(UserInfoFactory.getUserFullName(tmp" + className + ".getCreatorID()));");
    out.println("          tmp" + className + ".setUpdaterName(UserInfoFactory.getUserFullName(tmp" + className + ".getUpdaterID()));");
    out.println("          result.add(tmp" + className + ");");
    out.println("        }");
    out.println("        return(result);");
    out.println("      } catch (ApplicationException appEx) {");
    out.println("        throw appEx;");
    out.println("      } catch (SQLException sqle) {");
    out.println("        log.error(sqle, sqle);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());");
    out.println("      } catch (Exception e) {");
    out.println("        log.error(e, e);");
    out.println("        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);");
    out.println("      } finally {");
    out.println("        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }");
    out.println("        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }");
    out.println("      }");
    out.println("    }");
    out.println("  }");
    out.println();
  }

  public static String getIDFieldName() throws Exception {
    return("ID");
  }

  public static void openDBConn() throws Exception {
    Class.forName(driverStr);
    conn = DriverManager.getConnection(dbConnStr, dbUsername, dbPassword);

    sqlStat = "SELECT * FROM " + tableName.toUpperCase();
    stat = conn.createStatement();
    stat.setMaxRows(1);
    rs = stat.executeQuery(sqlStat);
    rsmd = rs.getMetaData();
  }

  public static void closeDBConn() throws Exception {
    try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
    try { stat.close(); } catch (Exception ignore) {} finally { stat = null; }
    try { conn.close(); } catch (Exception ignore) {} finally { conn = null; }
  }

  public static void printHeader() throws Exception {
    // Print the program header.
    Calendar now = Calendar.getInstance();
    String[] month = { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12" };
    out.println("/*");
    out.println(" * @(#)" + className + ".java");
    out.println(" *");
    out.println(" * Copyright (c) 2003 DCIVision Ltd");
    out.println(" * All rights reserved.");
    out.println(" *");
    out.println(" * This software is the confidential and proprietary information of DCIVision");
    out.println(" * Ltd (\"Confidential Information\").  You shall not disclose such Confidential");
    out.println(" * Information and shall use it only in accordance with the terms of the license");
    out.println(" * agreement you entered into with DCIVision Ltd.");
    out.println(" */");
    out.println("package " + packageName + ".dao;");
    out.println();
    out.println("import java.sql.*;");
    out.println("import java.util.*;");
    out.println("import com.dcivision.framework.*;");
    out.println("import com.dcivision.framework.bean.*;");
    out.println("import com.dcivision.framework.dao.*;");
    out.println("import com.dcivision.framework.web.*;");
    out.println("import " + packageName + ".bean.*;");
    out.println();
    out.println("/**");
    out.println("  " + className + "DAObject.java");
    out.println();
    out.println("  This class is the data access bean for table \"" + tableName.toUpperCase() + "\".");
    out.println();
    out.println("  @author      " + author);
    out.println("  @company     DCIVision Limited");
    out.println("  @creation date   " + (now.get(Calendar.DAY_OF_MONTH) < 10 ? ("0" + now.get(Calendar.DAY_OF_MONTH)) : ("" + now.get(Calendar.DAY_OF_MONTH))) + "/" + month[now.get(Calendar.MONTH)] + "/" + now.get(Calendar.YEAR));
    out.println("  @version     $Revision: 1.37.32.1 $");
    out.println("*/");
    out.println();

    out.println("public class " + className + "DAObject extends AbstractDAObject {");
    out.println();
    out.println("  public static final String REVISION = \"$Revision: 1.37.32.1 $\";");
    out.println();    
    out.println("  public static final String TABLE_NAME = \"" + tableName.toUpperCase() + "\";");
    out.println();
    out.println("  public " + className + "DAObject(SessionContainer sessionContainer, Connection dbConn) {");
    out.println("    super(sessionContainer, dbConn);");
    out.println("  }");
    out.println();
    out.println("  protected void initDBSetting() {");
    out.println("    this.baseTableName = TABLE_NAME;");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      out.println("    this.vecDBColumn.add(\"" + rsmd.getColumnName(i) + "\");");
    }
    out.println("  }");
    out.println();
  }

  public static void printFooter() throws Exception {
    out.println("}");
  }

  public static void openSrcFile() throws Exception {
    try {
      out = new PrintStream(new FileOutputStream(srcFileName, false), true);
    } catch (Exception e) {
      out = System.out;
    }
  }

  public static void closeSrcFile() throws Exception {
    try { out.close(); } catch (Exception ignore) {} finally { out = null; }
  }

  public static String getVariableName(String dbColName) {
    return(getVariableName(dbColName, false));
  }

  public static String getVariableName(String dbColName, boolean capInitFlag) {
    String result = "";
    String[] tmpAry = splitString(dbColName.toLowerCase(), "_");
    for (int i = 0; i < tmpAry.length; i++) {
      String tmp = Character.toUpperCase(tmpAry[i].charAt(0)) + tmpAry[i].substring(1);
      if ("Id".equals(tmp)) {
        result += "ID";
      } else {
        result += tmp;
      }
    }
    if (capInitFlag) {
      return(result);
    } else {
      if (result.toLowerCase().equals("id") && !capInitFlag) {
        return("id");
      } else {
        return(Character.toLowerCase(result.charAt(0)) + result.substring(1));
      }
    }
  }

  public static String getJavaType(ResultSetMetaData inRSMD, int idx) throws Exception {
    return(getJavaType(inRSMD, idx, false));
  }

  public static String getJavaType(ResultSetMetaData inRSMD, int idx, boolean initCapFlag) throws Exception {
    System.out.println(inRSMD.getColumnName(idx) + ":" + inRSMD.getColumnTypeName(idx) + ":" + inRSMD.getColumnType(idx) + ":" + inRSMD.getPrecision(idx));
    if ((inRSMD.getColumnType(idx)==Types.BIGINT) || (inRSMD.getColumnType(idx)==Types.INTEGER)) {      // int
      return(initCapFlag ? capStrInit(JAVA_INTEGER) : JAVA_INTEGER);
    } else if ((inRSMD.getColumnType(idx)==Types.FLOAT) || (inRSMD.getColumnType(idx)==Types.DECIMAL) || (inRSMD.getColumnType(idx)==Types.DOUBLE)) {   // double
      return(initCapFlag ? capStrInit(JAVA_DECIMAL) : JAVA_DECIMAL);
    } else if ((inRSMD.getColumnType(idx)==Types.VARCHAR) || (inRSMD.getColumnType(idx)==Types.CHAR) || (inRSMD.getColumnType(idx)==Types.LONGVARCHAR)) {  // String
      return(initCapFlag ? capStrInit(JAVA_STRING) : JAVA_STRING);
    } else if ((inRSMD.getColumnType(idx)==Types.DATE) || (inRSMD.getColumnType(idx)==Types.TIMESTAMP)) {  // Date
      return(initCapFlag ? capStrInit(JAVA_DATE) : JAVA_DATE);
    } else {
      return("<UNKNOWN>");
    }
  }

  public static String capStrInit(String str) {
    return(Character.toUpperCase(str.charAt(0)) + str.substring(1));
  }

  /**
   * splitString
   *
   * Returns string array which after spliting the input string.
   *
   * @param   src   String which going to be splitted.
   * @param   div   Delimitator for split.
   * @return    String array after split.
   */
  public static String[] splitString(String src, String div) {
    String[] result = null;
    java.util.Vector temp = new java.util.Vector();
    int idx1 = 0;
    int idx2 = 0;

    if (src == null || src.length() == 0 || div == null || div.length() == 0) {
    return(null);
    }

    try {
    while (idx2 >= 0 && idx1 < src.length()) {
      idx2 = src.indexOf(div, idx1);
      if (idx2 >= 0) {
      temp.addElement(src.substring(idx1, idx2));
      } else {
      temp.addElement(src.substring(idx1));
      }
      idx1 = idx2 + div.length();
    }

    result = new String[temp.size()];
    for (int i = 0; i < temp.size(); i++) {
      result[i] = (String)temp.elementAt(i);
    }

    return(result);
    } catch (Exception e) {
      log.error(e, e);
      return(null);
    }
  }

  /**
   * splitString
   *
   * Returns string array which after spliting the input string using space as delimitator.
   *
   * @param   src String which going to be splitted using space as delimitator.
   * @return    String array after split.
   */
  public static String[] splitString(String src) {
    return(splitString(src, " "));
  }

  /**
   * replaceString
   *
   * Returns string after replacing the original string to the target string, no matter
   * the ori string wrapped by space or charater.
   *
   * @param   src Source string which going to be replaced
   * @param   ori Original string in the source string
   * @param   tar Target string which replaces the original string
   * @return    String after replacement taken place.
   */
  public static String replaceString(String src, String ori, String tar) {
    String result = null;

    if (src!=null && ori!=null && tar!=null){
      try {
        StringBuffer source = new StringBuffer(src);
        int appearedIndex = 0;
        int oriLength = ori.length();
        int tarLength = tar.length();
        int byPassingIndex = 0;
        // If you got exception here, please check your JDK version. JDK 1.4 is needed for this method.
        while (source.indexOf(ori,byPassingIndex)!=-1) {
          appearedIndex = source.indexOf(ori,byPassingIndex);
          if (ori.equals(source.substring(appearedIndex,appearedIndex+oriLength))) {
            source = source.replace(appearedIndex,appearedIndex+oriLength,tar);
            byPassingIndex = appearedIndex+tarLength;
          } else {
            byPassingIndex = appearedIndex;
          }
        }
        result = source.toString();
      } catch (Exception e) {
        log.error(e, e);
      }
    }
    return result;
  }

}
